package com.scalar.chiptrack.operations.packingslip.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;

import com.scalar.ScalarException;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.operations.packingslip.FinalInventoryData;
import com.scalar.chiptrack.utils.SqlQueryMappings;
import com.scalar.chiptrack.utils.DateUtil;

public final class FinalInventoryDAO 
{
   
	public static void updateInventoryLotData (ArrayList finalInventoryDataList) throws ScalarException {
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean success = false;

		if ((finalInventoryDataList == null) || (finalInventoryDataList.isEmpty() == true)) {
			return;
		}
        
		try {
			conn = DataSource.getConnection();
                                   
			pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.packingslip.updateInventoryLotData"));
            
			FinalInventoryData inventoryData = null;
			int size = finalInventoryDataList.size();
            
			for (int i=0; i<size; i++) {
				inventoryData = (FinalInventoryData) finalInventoryDataList.get (i);
				pstmt.setInt (1, inventoryData.getLotId());
				pstmt.setInt (2, inventoryData.getCsLineItemId());
				pstmt.setInt (3, inventoryData.getQuantityPull());

				pstmt.addBatch();
			}
            
			pstmt.executeBatch();
			success = true;
		}
		catch (Exception sqle) {
			throw new ScalarException ("Could not update Inventory Lot table with balance quantity information", sqle);
		}
		finally {
			try {
				if (conn != null) {
					if (success == true) {
						conn.commit();
					}
					else {
						conn.rollback();
					}
				}
			}
			catch (SQLException ignore) {}

			if (pstmt != null) {
				try { pstmt.close(); } catch (SQLException ignore){}
			}
			if (conn != null) {
				try { conn.close(); } catch (SQLException ignore){}
			}
		}

	}
    
	public static ArrayList loadFinalInventoryDataByPartNumberAndLineItem( String partNumber, String lineItemId, String csLineItemId , String vendorAddressId, String substitutePSIds ) throws ScalarException
	{
		Connection conn = null;
		CallableStatement cstmt = null;
		ResultSet rset = null;
		ArrayList finalInventoryDataList = new ArrayList();

		if ( partNumber == null ) 
		{
			return null;
		}

		try {
			conn = DataSource.getConnection();

			cstmt = conn.prepareCall( "{call sp_load_final_inventory_by_partnumber_and_lineitem_new (?, ?, ?, ?, ?) }"  );
			cstmt.setString( 1, partNumber );
			cstmt.setString( 2, lineItemId );
			cstmt.setString( 3, csLineItemId );
			cstmt.setInt   ( 4, Integer.parseInt( vendorAddressId ) );
            cstmt.setString( 5, substitutePSIds );

			rset = cstmt.executeQuery();
			FinalInventoryData finalInventoryData = null;

			while( rset.next() ) 
			{
				finalInventoryData = new FinalInventoryData(   Integer.parseInt( csLineItemId ),
															   rset.getString( "Lot_Part_Number" ),
															   rset.getString( "Lot_Number" ),
															   rset.getString( "Fab_lot_Number" ), 
															   rset.getInt	 ( "Lot_ID" ),
															   rset.getInt	 ( "Location_ID" ), 
															   rset.getString( "Location" ),
															   rset.getString( "Lot_Date_Code" ),
															   rset.getString( "Step_Name" ),
															   rset.getDate  ( "Date_In" ),
															   rset.getInt	 ( "Qty_In" ),
															   rset.getInt	 ( "Balance_Qty" ),
															   rset.getInt	 ( "Reserved_Qty" ),
															   rset.getInt	 ( "Quantity_Pull" ) );

                finalInventoryData.setLotEnteredBy ( rset.getString ( "Lot_Entered_By" ) );
                finalInventoryData.setHiddenBalance(  rset.getInt    ( "Balance_Qty" )  );
                finalInventoryData.setIsHold       ( rset.getInt    ( "Lot_Manual_Hold" ) );
                finalInventoryData.setDateCodeList( DateUtil.getDefaultDateCodeList(true) );
                
				finalInventoryDataList.add( finalInventoryData );
			}
		}
		catch (SQLException se) 
		{
			//se.printStackTrace();
			if (se.getMessage().indexOf("No ResultSet") == -1) {
				throw new ScalarException ("Could not retrieve FinalInventoryData list from database", se);
			}
            
			//System.out.println ("No FinalInventoryData for manufacturing Part Number: " + mktPartNumber);
		}
		catch (Exception e) 
		{
			e.printStackTrace();
			throw new ScalarException ("Could not retrieve FinalInventoryData list from database", e);
		}
		finally 
		{
			if (rset != null) {
				try { rset.close(); } catch (SQLException ignore) {}
			}
			if (cstmt != null) {
				try { cstmt.close(); } catch (SQLException ignore) {}
			}
			if (conn != null) {
				try { conn.close(); } catch (SQLException ignore) {}
			}
		}

		return finalInventoryDataList;
	}

	public static ArrayList loadFinalInventoryDataByPartNumberAndLineItem( String mktPartNumber, String lineItemId, String csLineItemId ) throws ScalarException 
	{
		Connection conn = null;
		CallableStatement cstmt = null;
		ResultSet rset = null;
		ArrayList finalInventoryDataList = new ArrayList();

		if (mktPartNumber == null) {
			return null;
		}

		try {
			conn = DataSource.getConnection();

			cstmt = conn.prepareCall( SqlQueryMappings.get( "operations.packingslip.loadFinalInventoryDataByPartNumberAndLineItem" ) );
			cstmt.setString( 1, mktPartNumber );
			cstmt.setString( 2, lineItemId );
			cstmt.setString( 3, csLineItemId );
            
			rset = cstmt.executeQuery();    
			FinalInventoryData finalInventoryData = null;

			while( rset.next() ) 
			{
				finalInventoryData = new FinalInventoryData(   Integer.parseInt( csLineItemId ),
															   mktPartNumber, 
															   rset.getString( "Lot_Number" ),
															   rset.getString( "Fab_lot_number" ), 
															   rset.getInt( "Lot_ID" ),
															   rset.getInt( "Location_ID" ), 
															   rset.getString( "Location" ),
															   rset.getString( "Lot_Date_Code" ),
															   rset.getString( "Step_Name" ),
															   rset.getDate( "Date_In" ),
															   rset.getInt( "Qty_In" ),
															   rset.getInt( "Balance_Qty" ),
															   rset.getInt( "Reserved_Qty" ),
															   rset.getInt( "Quantity_Pull" ) );

				finalInventoryDataList.add( finalInventoryData );
			}
		}
		catch (SQLException se) 
		{
			//se.printStackTrace();
			if (se.getMessage().indexOf("No ResultSet") == -1) {
				throw new ScalarException ("Could not retrieve FinalInventoryData list from database", se);
			}
            
			//System.out.println ("No FinalInventoryData for manufacturing Part Number: " + mktPartNumber);
		}
		catch (Exception e) 
		{
			e.printStackTrace();
			throw new ScalarException ("Could not retrieve FinalInventoryData list from database", e);
		}
		finally 
		{
			if (rset != null) {
				try { rset.close(); } catch (SQLException ignore) {}
			}
			if (cstmt != null) {
				try { cstmt.close(); } catch (SQLException ignore) {}
			}
			if (conn != null) {
				try { conn.close(); } catch (SQLException ignore) {}
			}
		}

		return finalInventoryDataList;
	}

	public static HashMap loadDropShipmentLotsByPartNumberAndLineItem( String mktPartNumber, String locationId )//, String csLineItemId ) 
		throws ScalarException 
	{
		Connection conn = null;
		CallableStatement cstmt = null;
		ResultSet rset = null;
		HashMap dropShipmentDataListMap = new HashMap();
		ArrayList dropShipmentDataList = new ArrayList();

		if (mktPartNumber == null) {
			return null;
		}

		try {
			conn = DataSource.getConnection();

			cstmt = conn.prepareCall( SqlQueryMappings.get( "operations.dropshipment.loadDropShipmentLotsByPartNumberAndLineItem" ) );
			cstmt.setString( 1, mktPartNumber );
			cstmt.setInt( 2, Integer.parseInt(locationId.trim()));
			rset = cstmt.executeQuery();    
			FinalInventoryData finalInventoryData = null;
			//Lot lot = null;
			while( rset.next() ) 
			{
				finalInventoryData = new FinalInventoryData ();
				finalInventoryData.setLotId(rset.getInt( "Lot_ID" ));
				finalInventoryData.setLotNumber(rset.getString( "Lot_Number" ));
				String locationDetails = ""+rset.getInt( "Location_ID" )+"$"+rset.getString( "Step_Name" )+" - "+rset.getString( "Location" ); 
				finalInventoryData.setLocationDetails (locationDetails);
				if (dropShipmentDataListMap.containsKey(locationDetails))
				{
					dropShipmentDataList.add( finalInventoryData );
				}
				else
				{
					dropShipmentDataList = new ArrayList();
					dropShipmentDataList.add( finalInventoryData );
				}
				dropShipmentDataListMap.put(locationDetails, dropShipmentDataList);
			}
		}
		catch (SQLException se) 
		{
			if (se.getMessage().indexOf("No ResultSet") == -1) {
				throw new ScalarException ("Could not retrieve DropShipmentData list from database", se);
			}
		}
		catch (Exception e) 
		{
			e.printStackTrace();
			throw new ScalarException ("Could not retrieve DropShipmentData list from database", e);
		}
		finally 
		{
			if (rset != null) {
				try { rset.close(); } catch (SQLException ignore) {}
			}
			if (cstmt != null) {
				try { cstmt.close(); } catch (SQLException ignore) {}
			}
			if (conn != null) {
				try { conn.close(); } catch (SQLException ignore) {}
			}
		}
		return dropShipmentDataListMap;
	}
	
	public static ArrayList getFinishedGoodsLocations( String locId ) throws ScalarException
	{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ArrayList locations = null;
		FinalInventoryData finalInventoryData = null;

		try
		{
			conn = DataSource.getConnection();
			pstmt = conn.prepareStatement (SqlQueryMappings.get("operations.dropShipment.getFinishedGoodsLocations"));
			pstmt.setInt (1, Integer.parseInt (locId.trim() ));
			rs = pstmt.executeQuery();
			
			locations = new ArrayList();

			while (rs.next())
			{
				finalInventoryData = new FinalInventoryData();
				int locationId = rs.getInt("location_ID");
				finalInventoryData.setLocationId(locationId);
				finalInventoryData.setLocationDetails(locationId+"$"+rs.getString("Location_Details"));
				locations.add(finalInventoryData);
			}
		}
		catch (Exception sqlex)
		{
			sqlex.printStackTrace();
			throw new ScalarException ("Could not get locations from database", sqlex);
		}
		finally
		{
			if (rs != null) {
				try { rs.close(); } catch (SQLException excp){}
			}
			if (pstmt != null) {
				try { pstmt.close(); } catch (SQLException excp){}
			}
			if (conn != null) {
				try { conn.close(); } catch (SQLException excp){}
			}
		}

		if(locations.size() == 0) {
			finalInventoryData = new FinalInventoryData();
			locations.add(finalInventoryData);
		}

		return locations;
   }

    public static String getProcessStepIdsAsString ( String shippedPartNumber ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;

        StringBuffer shippedPartNo1 = new StringBuffer();
        String       shippedPartNo2 = null;
        String       searchString   = "";

		try
        {
            //String[] emailArray = StringUtils.toStringArray( emails, Mailer.EMAIL_SEPARATOR );
            if( shippedPartNumber.indexOf(",") > 0 )
            {
                searchString = shippedPartNumber.replaceAll( "," , "','");
            }
            else
            {
                searchString = shippedPartNumber;
            }
            conn = DataSource.getConnection();
            //pstmt = conn.prepareCall ( "SELECT DISTINCT Part_Number, ProcessStep_ID FROM Product WHERE Part_Number IN ('" + searchString + "')" );
            pstmt = conn.prepareCall ( "SELECT DISTINCT P.Part_Number, P.ProcessStep_ID FROM Product P INNER JOIN Process_Step ON P.ProcessStep_ID = Process_Step.ProcessStep_ID WHERE P.Part_Number IN ('" + searchString + "') AND Process_Step.PS_Type_Name = 'Finished Goods'" );
            rset = pstmt.executeQuery();

            while ( rset.next() )
            {
                //partNumberList.add( rset.getString ( "ProcessStep_ID" ) ) ;
                shippedPartNo1.append( rset.getString ( "ProcessStep_ID" ) );
                shippedPartNo1.append( "," );
            }
            if( shippedPartNo1.toString().trim().length() > 0 )
            {
                shippedPartNo2 = shippedPartNo1.substring ( 0 , shippedPartNo1.toString().trim().length() - 1 );
            }
		}
		catch (Exception e) {
			throw new ScalarException ("Could not parse the Shipped Part Numbers from database", e);
		}
        finally {
			if (rset != null) {
				try { rset.close(); } catch (SQLException ignore) {}
			}
			if (pstmt != null) {
				try { pstmt.close(); } catch (SQLException ignore) {}
			}
			if (conn != null) {
				try { conn.close(); } catch (SQLException ignore) {}
			}
		}

		return shippedPartNo2;
	}


}